Data cache techniques in support of synchronization of databases in a distributed environment

ABSTRACT

A method for synchronizing a database server to a current status of a client database using data cache techniques is provided. Upon initiating synchronization, the database server receives an identification of a client connection, and creates a server-cache-table having a structure that is compatible with the client database. Database records from a corresponding client-cache-table are received through the client connection and inserted into the server-cache-table. The server-cache-table is merged into the database server. Similar cache-tables can be utilized to synchronize data from the database server to the client. Optionally, a parent-cache-table can be created on the server and populated with records associated with the identified client. Queries and other database operations during synchronization can be performed over the parent-cache-table to further optimize performance.

This patent application is a divisional application of U.S. applicationSer. No. 11/925,585, filed Oct. 27, 2007 which claims the benefit ofpriority under 35 U.S.C. §119(c) of U.S. Application Ser. No.60/867,943, filed Nov. 30, 2006, entitled “Data Cache Techniques inSupport of Synchronization of Databases in a Distributed Environment,”and of U.S. Application Ser. No. 60/863,243, filed Oct. 27, 2006,entitled “Territory Management System,” which are hereby incorporated byreference in their respective entireties.

FIELD OF THE INVENTION

The present invention relates to the synchronization of databases in adistributed environment, and, more particularly, to software systems andmethodologies that optimize database synchronization through the use ofdata cache techniques.

BACKGROUND OF THE INVENTION

In many sectors, field representatives are used to educate customers andpotential customers on the products of manufacturers and serviceproviders. In the course of their duties, sales representatives makesite visits and build a relationship with the customers and potentialcustomers in their assigned territory. Typically, field representativesmaintain records of their visits in accordance with any guidelines orrequirements of the companies that they represent. The records of sitevisits can include details and have formats that vary widely amongrepresentatives and the companies they represent. The data collected byfield representatives is typically aggregated in a centralizedrepository such as a database server so that the data can be analyzed.Additionally, the centralized repository commonly redistributesinformation or provides additional information, such as analysis of thedata, to the field representatives. This data collection anddistribution is typically performed using a client-server databasearchitecture.

As the number of field representatives increases, the performance andscalability of typical client-server database models can degrade, and insome cases render the system inoperable. These performance issues areespecially problematic when multiple clients simultaneously attempt tosynchronize with the server. Multiple simultaneous client connectionsare common in environments in which clients synchronize during their“down-time” (e.g., overnight, when the user is not visiting sites andentering data), because users typically have similar schedules.

The performance and scalability degradation results from severalfactors. In a client-server model, database query optimizers do notpredictably perform transactions on the client or the server. This canresult in a great discrepancy in the amount of data transmitted over thenetwork compared to the amount of data that is actually required to bemodified or added. For example, during synchronization, the client mayneed to insert only a few new records into the tables on the databaseserver. However, rather than transmitting the few records from theclient to the server, the server may transmit the entire table to theclient, perform the insertion on the client, and then transmit the tableback to the server. Such a transaction greatly increases the quantity ofdata transmitted over the network, and can lock the entire databaseserver until the transaction is complete, thereby preventing anotherclient from synchronizing at the same time.

Some systems have attempted to address this problem by creatingadditional infrastructure external to the client-server database. Forexample, the synchronizing machine can create a queue into which the newor modified records are inserted. The queue is then processed bytransmitting each record to the other machine where it is processed.This approach is limited by the additional processing time that isrequired during execution because it is built on-top-of the database andbecause it requires sequential processing of the queue rather thantaking advantage of the built-in services of the database.

The present invention addresses these deficiencies in the art andprovides other improvements useful to the synchronization ofclient-server databases.

SUMMARY OF THE INVENTION

In accordance with one aspect of the present invention, a method forsynchronizing databases is provided over a communication connection thatis established between a database server and a client. Aclient-cache-table is created on the client having one more fieldscorresponding to at least one field in the client database. Theclient-cache-table is populated with at least one database record inaccordance with a synchronization criterion. A server-cache-table isalso created on the database server, such that the server-cache-tablehas one or more fields corresponding to at least one field in theclient-cache-table. At least one record in the populatedclient-cache-table is inserted into the server-cache-table, and theserver-cache-table is merged into the database server.

In accordance with a further aspect of the present invention, thedatabases of a client and a database server can be synchronized. Acommunication connection between the database server and the client isestablished and the client is identified to the database server. Aclient-cache-table is created on the client and at least one databaserecords from a results-cache-table on the database server is insertedinto the client-cache-table. The client-cache-table is then merged intothe client database.

In accordance with yet a further aspect of the present invention, thetables on the database server include at least one parent table andassociated child tables. The parent table and associated child tablesare populated with database records that are associated with at leastone client. A parent-cache-table is created on the server for at leastone of the parent tables and is associated with the client. Theparent-cache-table is populated with at least one of the databaserecords associated with the client. Furthermore, the results-cache-tableis populated with at least one database record from theparent-cache-table and associated child tables. Optionally, the databaserecords that populate the results-cache-table further satisfy asynchronization criterion.

These and other aspects, features, and advantages will be apparent fromthe following description of certain embodiments and the accompanyingdrawing figures.

DESCRIPTION OF THE DRAWING FIGURES

FIG. 1 is a flow diagram illustrating certain steps in accordance withan environment in which the present invention may used, wherein fieldrepresentatives synchronize their data with information managed on aserver;

FIG. 2 is illustrates a network environment in which a systemimplementing an embodiment of the invention can be used;

FIG. 3 is a flow diagram illustrating certain steps in accordance with apreferred embodiment of the present invention in which data on a clientis synchronized with a database server; and

FIG. 4 is a flow diagram illustrating certain steps in accordance with apreferred embodiment of the present invention in which data on a serveris synchronized with a client database.

DETAILED DESCRIPTION OF CERTAIN EMBODIMENTS

By way of overview and introduction, the present invention is discussedbelow with respect to a territory management system that providesrepresentatives with a tool for conducting their activities in anefficient manner. Such a territory management tool typically requiressynchronization of a central database with one or more client databasesowned and operated by the field representatives. Furthermore, the clientdatabase frequently requires synchronization with information createdand stored on the database server, or uploaded to the database serverduring the synchronization of a different client.

The management system is preferably implemented as a software-basedsystem, having components executing on a number of systems including acentral computer and a multiplicity of remote machines, with eachrepresentative having a remote machine for his or her personal use andfor forwarding adverse event information to a location associated withor accessible by the central computer. Without loss of generality, thepresent invention is described in relation to a particularrepresentative using a single remote machine in the course of his or heractivities covering an assigned territory. In the preferred embodimentdescribed below, the representative is bespoke or contracted to apharmaceutical manufacturer, and the representative “covers” a territorythrough visits to health care providers (“HCPs”), physicians and nurses(collectively, more generally, “prescribers”) at which therepresentative is able to discuss and promote the use of themanufacturer's products. However, the invention has industrialapplicability in other sectors including in connection with fieldrepresentative activities in promoting products and services of generalnature, including, without limitation, in the medical device sector.

A preferred software tool for territory management is described in partin the aforesaid U.S. Provisional Application Ser. No. 60/863,243,entitled “Territory Management System.” The Territory Management Systemsoftware provides through a Web-browser interface a number of tools thatassist the representative in planning, recording, and trackingactivities with customers such as prescribers. In relevant part, theTerritory Management System software can assist a representative withmanaging, processing and deciphering sales information following theirefforts in servicing his or her assigned territory. The TerritoryManagement System software includes additional features that can assistrepresentatives in other ways, such as in complying with concerningcertain regulatory requirements or state or federal constraintsconcerning gifts to health care professionals, in reducing discrepanciesin sample inventories as between stored and on-hand inventories, and inscheduling site visits to HCPs. Various features of the TerritoryManagement System software are described in certain co-pendingprovisional patent applications which are referenced below, to theextent they have pertinence to the discussion of the present invention.

The remote machine used by the representative includes a suitablecomplement of hardware and software including, by way of example, aprocessor, memory, an accessible database, communications equipment, andinput/output devices.

The process 100 illustrated in FIG. 1 concerns activities relating to apharmaceuticals representative. In the area of pharmaceuticalrepresentation, it is common for a representative to drop-in on aprescriber within the representative's assigned territory with the hopethat the prescriber will be able to speak with the representative for afew minutes. The nature of such visits can vary from informal to formal,but one objective of the representative is to promote a prescriber'sfamiliarity and understanding of pharmaceuticals that the representativewishes the prescriber to prescribe to patients. In other fields, therepresentative similarly has an objective of promoting purchase and useof his or her represented products and services through educational andpromotional efforts. The process 100 includes activities by both a salesrepresentative operating a client database (illustrated in the leftbranch of the flow diagram) and a database server (shown in the rightbranch). The activities of these persons, and data capture describedbelow are brought together, in accordance with a salient aspect of theinvention, to transmit and synchronize the data collected by the userswith the server and redistribute relevant information back to the clientdatabases.

Thus, at block 105, the representative makes a site visit to aparticular prescriber's office. The representative typically makes anumber of visits during the course of a day, whether scheduled ordrop-in, and these visits can be coordinated through a calendar functionprovided by the same programmed system that handles the representative'sreporting of any expense information. The scheduling and calendarfunctions form no part of the present invention, but are described inco-pending U.S. Provisional Application Ser. No. 60/868,015, filed onNov. 30, 2006, entitled “Cycle Planning Tool for Territory Management”and U.S. Provisional Application Ser. No. 60/868,027, filed on Nov. 30,2006, entitled “Itinerary Search Tool for Territory Management,” whichare each hereby incorporated by reference in their respectiveentireties.

It may be that the prescriber is not available for a conference with therepresentative, in which case the representative proceeds to a nextappointment by traveling to another site within his or her territory,preferably with guidance from a calendared set of appointments that fillthe day. If, however, a conference is held with a prescriber at aparticular site visit, then the representative is supposed to capture asummary of who was met, what products (services) were discussed, whethersamples were provided (and details concerning same), whether any adverseevent information was discussed, propose a next meeting and follow-updiscussion points, and document any expenses attributable to anyprescribers that were visited. This information is captured in a “callnote,” which, upon entry into his or her machine, is a “call completion”concerning a sales call made at a given date and time. Functionality,systems and methodologies suited to adverse-event data capture,management and reporting are described in co-pending U.S. ProvisionalApplication Ser. No. 60/867,923, filed on Nov. 30, 2006, entitled“Adverse Event Data Capture Software, Systems, And Methodologies” (“AECapture”), which is hereby incorporated by reference in its entirety.Functionality, systems and methodologies suited to expense capture aredescribed in co-pending U.S. Provisional Application Ser. No.60/867,906, filed Nov. 30, 2006, entitled “Pharmaceutical RepresentativeExpense Report Management Software, Systems, And Methodologies,” whichis hereby incorporated by reference in its entirety. All of thisinformation must ultimately be synchronized with the central database.

Representatives generally seek to satisfy any concerns expressed by thecustomers in their territory, and so follow-up points can be captured inthe territory management software so that the representative can betteraddress those concerns with their customers. Representatives also haveobligations to the company they represent to document any expensesattributable to visits to their customers and any adverse events thatmay have been reported for investigation or follow-up. Again, thisinformation ultimately must be synchronized with the central database.

Use of the territory management system commences then at block 110 atwhich the representative initiates the entry of visitation notes, knownas making a “call completion” because he or she has completed a salescall to the prescriber/customer and has meeting notes to record. Theuser can initiate this data entry process in a variety of ways. Forexample, a calendar feature of the management system can present an iconsuch as a telephone icon for selection which causes a call-notes entryform to be displayed. Alternatively, the calendar feature of themanagement system can include entries showing the time, location, orboth, of the visits for that day which the representative can select tobring up the call-notes entry form. Yet another alternative is that therepresentative can call up a prescriber summary page which can include alink (or an icon as mentioned above) to enter call notes that are to beassociated with that prescriber visit. These features are described inthe aforementioned A/E Capture disclosure, and are not described furtherherein.

At block 110, the user inputs call notes and any adverse informationusing a suitable data entry form, such as the call-notes entry formdescribed in the aforementioned AE Capture disclosure, which is recordedinto the local database on the user's machine.

At step 120, the determination is made whether the database should besynchronized with the server. Synchronization can be initiated inmultiple ways. For example, the user can instruct the client computer tosynchronize with the database server, synchronization can be scheduledfor a particular time or after a specified time interval, or the clientdatabase can determine that the certain one or more synchronizationcriterion have been met, such as a period of inactivity or networkconnectivity.

If synchronization is not appropriate at 120, the user and the clientreturn to step 105, conceptually, to continue the process of visitingsites. However, if synchronization is initiated, the client proceeds tostep 160, identifying the process illustrated in FIGS. 3 and 4.

While the user is conducting site visits 105 and recording call notes,any adverse event information 110, and other relevant information, thedatabase server can perform maintenance and administrative tasks. Forexample, database maintenance 130 can include consistency checks,backups, and can re-compute database indices. Additionally, at block135, manager review of the database can be performed through userinteraction or automated algorithmic program code. Additionally,automated reports can be processed at step 140.

At step 150, the database server can receive a request to synchronizewith a client database. If no synchronization request is received, thedatabase server continues to perform its administrative tasks asnecessary (e.g., steps 130, 135, and 145.) However, if a synchronizationrequest is received, the server proceeds to step 160, identifying theprocess illustrated in FIGS. 3 and 4.

Database synchronization can be conducted over a heterogeneous network210 as illustrated in FIG. 2. Multiple clients can connect to server 270over the network 210 using various means of communication. For example,a laptop 220A can connect to a modem 230 to access the database server270. Alternatively, laptop 220B can connect to the network over anEthernet or other broadband connection.

Additionally, a variety of wireless networks can be utilized tofacilitate client-server communications and database synchronization.Laptop 220C and tablet PC 240B can connect via an internal cellularmodem or PC Card cellular modem. Tablet PC 240A or Personal DigitalAssistant (PDA) 230B can connect to a wireless access point 250 tocommunicate with server 270. Alternatively, PDA 230A can connect using awireless standard such as Bluetooth or via a wired link to a cellphone260 that can communicate over the network 210 with the server 270.

The present invention can be implemented over a client server network aswell as a peer-to-peer network. If operated over a peer-to-peer network,multiple databases at each respective client can be synchronized andupdated to account for the data input or modified on the peer clients.However, for the purposes of expediency, in the below discussion, eachactor in a network transaction is referred to as a client or a server.

Additionally, it should be noted that FIG. 2 illustrates clients asdevices conventionally associated with client-type operations andperformance (e.g., laptops, tablet computers, etc.). However, a clientcan include other communication devices and combinations thereof. Forexample, a client can be separated into a thin element (e.g., a personaldigital assistant (PDA) that acts only as a display) that communicateswith a workstation element (e.g., a laptop or other portable computer).Through a configuration such as the one described, a fieldrepresentative can carry the PDA into a customer's and enter theinformation on the PDA, which is communicated to the portable computerlocated in the field representative's car. Alternatively, a computerizedmedical device can also act as a client that synchronizes its data witha server.

Each type of connection (e.g., modem, cellular network, broadband, etc.. . . ) and device has its own connection characteristics, such asspeed, availability, and reliability. The database synchronization setforth by the present invention can function over these types ofcommunication links and communication devices, as well as many others.

The process of database synchronization is illustrated in FIGS. 3 and 4.FIG. 3 illustrates a process in accordance with an embodiment of thepresent invention by which data that has been added or modified on theclient is transmitted to the server 270. FIG. 4 illustrates a process inaccordance with an embodiment of the present invention by whichinformation is distributed from the server 270 to the client.Synchronization to or from the server 270 can occur in any order (i.e.,the process of uploading information to the server 270 can occur beforeor after download information from the server 270). Moreover, the uploadand download can occur simultaneously to the extent database consistencyand associated data locks allow.

With reference now to FIG. 3, the process 300 illustrates the exchangeof data from the client to the server. At step 310 a communicationconnection between the client and the server is established. Either theclient or the server can initiate the communication connection. However,because clients typically have intermittent connectivity and can changenetwork addresses frequently, the connection is preferably initiated bythe client.

The connection is preferably a database link, such as that provided bythe Oracle database. The database link allows for the execution of SQLor other standard database language instructions to operate on remotedatabases. Thus, for example, the database link allows the client to“INSERT” a record on the database server. Other protocols and languages,known in the art, that provide similar functionality can be used aswell.

Preferably, the client and server communicate to determine the lastsynchronization (“sync”) time between the two machines at step 320.Determining the last sync time can further reduce the overhead andprocessing required for synchronization by eliminating any records thathave not been modified or added since the last sync time. However, ifdesired a full synchronization can be executed. Full synchronization canbe useful if one of the databases has been corrupted. The uncorrupteddatabase can synchronize and overwrite the corrupted records of thedatabase even if the system indicates the records were previouslysynchronized.

The last sync time can be maintained by the client or the server. If theclient stores the last sync time, the client can log the date and timeof the most recent synchronization and provide the time to the server orrespond with the time at the request of the server. Alternatively, theserver can maintain a record of each client and the last times theclient synchronized with the database server and, thereby, provide thelast sync time to the client in a similar manner. Optionally, both theserver and client can maintain a record of the last sync time andexchange that time. If there is a discrepancy, the earlier time ispreferably utilized as the last sync time. Using the earlier sync timecan result in possible redundancy of data. However, this redundancy canbe accounted for and eliminated on the machine receiving the data bydetermining the data is already in the database. Utilizing the earliersync time prevents data from being incorrectly excluded from asynchronization transaction.

Once the last sync time is determined a determination can be made as towhether synchronization is necessary at step 330. If neither the clientnor the server has modified any database records since the last synctime, then database synchronization is not necessary, and the client andserver can proceed to step 390 and update the last sync time.

However, if synchronization is necessary, the client and server canoptionally communicate to determine which tables are necessary tosynchronize at step 340. This determination can include several factors.For example, certain tables on the server can be server-specific andtherefore, not necessary for the client to perform its function and evenpose potential security risks if the information stored therein weretransmitted to the client. Additionally, certain data may be scheduledfor synchronization on a periodic basis that is less frequent than everysynchronization transaction.

After the last sync time, and optionally after synchronization tableshave been identified, the client and server can execute several steps ofthe synchronization process in parallel and independently for moreefficient processing. FIG. 3 illustrates the steps executed on theclient with the suffix “C” on the left-side of the process-flow andsteps executed on the server with the suffix “S” on the right-side ofthe process-flow.

At step 350C, the client creates a client-cache-table to store therecords, or copies of the records, that are to be synchronized with thedatabase server. Client-cache-tables can comprise traditional(permanent) database tables that are populated and cleared for eachsynchronization session. Alternatively, client-cache-tables can be afile or data-structure stored in the computer's memory. However, theclient-cache-table preferably comprises a temporary table. Temporarytables are designed to hold session-private data that exists only forthe duration of a transaction or session. Furthermore, data locks arenot required for the data of the temporary tables because each sessionhas its own private data. A client-cache-table is preferably created foreach table that is identified as requiring synchronization. Theclient-cache-tables to be created are in response to the determinationat step 340.

The client data records that satisfy synchronization criteria areidentified at step 360C. Synchronization criteria can include one ormore test or rule. Preferably, one such synchronization criterionidentifies the data record as being modified, added, or deleted afterthe last sync time. Synchronization criteria can also include the typeof data, the owner of the data, or the priority of the data. The datarecords satisfying the synchronization criteria are then populated inthe client-cache-table at step 370C.

The server can prepare to receive the data records from the client,while the client is preparing its client-cache-table 350C, identifyingthe records to be synchronized 360C, and populating theclient-cache-table with those records 370C. At step 350S, the servercreates a server-cache-table in response to the determination at step340 to receive and store the synchronization records from the client. Aserver-cache-table is preferably created for each table that isidentified as requiring synchronization.

Creating client-cache-tables and server-cache-tables on the client andserver, respectively, isolates the main client and/or database servertables from the network database transactions. In doing so, the databasequery optimizer can make better decisions regarding how to transfer thedata from the client to the server, or vice versa. Furthermore, thecreation of cache tables reduces the amount of data that must beprocessed by the client and/or the server and be transmitted over thenetwork. These, and other factors, can increase performance as the sizeof the dataset stored in the client database and/or server increases, asthe number of clients increases, and as bandwidth of the network orprocessing power of the client and/or server decreases, among othercomputing environmental conditions.

Thus, once the cache tables on the client and server have been createdand the client-cache-table populated, the client and server canefficiently perform the transfer of unsynchronized data. At step 370Cthe records stored in the client-cache-table that are to be synchronizedwith the server are selected, and at step 380S, the records are insertedinto the server-cache-table after being transmitted over the network(Data Transmission). Preferably, the selection and insertion of data areexecuted through a standard database language command, such as a SQLcommand, and transmitted over a database link.

Once the client data records from the client-cache-table have beeninserted into the server-cache-table at step 370S, theserver-cache-table can be merged into the tables of the database serverat step 380S. The merge process can be executed through a merge-commanddefined by the database language of the system. The merger can insert,or create, new records, update existing records, and delete existingrecords. A records can be deleted by marking a particular field of therecord as null. The null field can be used to indicate the deletion.Alternatively, if the null field is in records in the cache-table, itcan be used instruct the database server to delete the record from thedatabase when the merge is performed.

Optionally, the merge process can identify duplicate records, andcorrectly ignore or accept the duplicate records. Duplicate records canresult from various conditions. For example, duplicate records canresult if the last sync time is incorrect due to a disconnection of asynchronization session prior to updating the last sync time (step 390)and after the merge step at 380S.

The synchronization transaction is preferably logged at step 385S.Logging the transaction allows for auditing if an error occurs.Additionally, a log can be used to “replay” the transactions to recreatea database if there is an error or lost data.

Preferably, the client is notified at step 390 that the transaction hascompleted and been logged. This notification can be implicit in thedatabase link transaction that selects the client-cache-table datarecords and inserts the records into the server-cache-table. However,additional notification of completion can be transmitted as anadditional verification or to confirm with non-database applicationlayer software that the synchronization is complete, e.g., as an e-mailor other electronic message to the client.

The last sync time is update at step 390. As discussed above, the lastsync time can be stored on the client, the server, or both. Preferably,the sync time is the time the data was copied from the client tables tothe client-cache-table. Thus, any data records that are modified orcreated on the client during synchronization will be timestamped afterthe last sync time and therefore synchronized during the nexttransaction.

The cache-tables are preferably cleared one the transaction is complete.Some database implementations automatically clear temporary tables oncethe particular session associated with the cache-tables is closed.Alternatively, the system can purposefully clear the cache table afterthe transaction has been committed or if the communication connection islost prior to committing the transaction.

The foregoing discussion focuses synchronizing on data from the clientto the server, and discusses the mechanisms used to efficiently effectthe synchronization. Similar mechanisms can be utilized to synchronizedata from the server to the client. The following discussion describesthe process of synchronizing server data to the client as illustrated inFIG. 4.

As discussed with respect to FIG. 3, a communication connection,preferably a database link, is established between the client anddatabase server at step 410. The client preferably identifies itself tothe database server at 415. Identification can occur through variousprocesses such as transmitting an ID to the database server, a challengeresponse, or by cryptographically signing messages to the databaseserver and authenticating the signature.

The last sync time is determined at step 420. The server can determinethe last sync time utilizing the identification of the client determinedat 415. Alternatively, the last sync time can be determined in any ofthe ways discussed above with respect to step 315. Further, thedetermination of whether synchronization is made at step 430. Whensynchronization is not necessary, the last sync time is updated at step490. However, if synchronization is required, the client and serverpreferably determine and identify the database server tables necessaryto examine during synchronization.

As discussed above, the client and database server can execute varioussteps of the synchronization process in parallel and independently formore efficient processing. FIG. 4 illustrates the steps executed on theclient with the suffix “C” on the left-side of the process-flow andsteps executed on the server with the suffix “S” on the right-side ofthe process-flow.

In this scenario, data is being transferred from the server to theclient, rather than client to server as described with respect to FIG.3. Therefore, in FIG. 4, the client performs similar steps as thoseperformed by the server in FIG. 3 in preparation for receiving the datato be synchronized. At step 450C, the client createsclient-cache-tables, preferably for each of the tables beingsynchronized, in response to the determination at step 440.

On the server, cached data can be utilized in multiple ways to increasethe speed and efficiency of synchronization of data from the server tothe client. At 460S, the server creates a results-cache-table, also inresponse to the determination at step 440, that stores the data to besynchronized with the client during the database link transaction. Inaccordance with one embodiment of the present invention, the server canmirror the steps of the client during the synchronization of data fromthe client to the server by proceeding to identify the data recordssatisfying the synchronization criteria at 470S, and populating theresults-cache-table with those data records at step 475S. The datarecords stored in the results-cache-table are then selected and insertedinto the client-cache-table on the client at step 480.

Alternatively, the server can further optimize synchronization byemploying additional cache tables. For example, as illustrated in FIG.4, the server can create one or more parent-cache-tables at step 450S,each having child tables storing other information relevant to theclient. Parent-cache-tables can be used to create smaller, moreefficient tables on which to operate and execute database queries. Whena database table stores millions of records for hundreds of clients,operations concerning a specific client still require searches over themillions of records. In the scenario described above, in which thequeries are known to be limited to a specific client, aparent-cache-table can be created for each parent table at 450S,associated with the specific client at 464S, and populated with only therecords pertaining the identified client at 466S, thereby greatlyreducing the computation required to execute the queries. The step ofassociating the parent-cache-table with the identified client at 464S ispreferably accomplished by the infrastructure of the database server,for example through the use of temporary tables in which thetemporary-patent-cache-table can be associated with a specific clientsession automatically by the database server.

If parent-cache-tables are utilized to prepare the data synchronizationwith the client, the identification of records satisfying the searchcriteria at step 470S can be performed over the set ofparent-cache-tables, child tables, and, optionally or selectively, anydatabase tables for which parent-cache-tables have not been created.

The synchronization criteria used to identify data records forserver-to-client synchronization can be similar to those ofclient-to-server synchronization. Additionally, synchronization criteriacan be used to assist in re-aligning the data for which each client isresponsible. The processes, systems, and methodologies of re-aligningdata responsibility and/or ownership form no part of the presentinvention, but are described in co-pending U.S. Provisional ApplicationSer. No. 60/867,945, filed on Nov. 30, 2006, entitled “Software, Systemsand Methodologies For Realignment of Remote Databases By A CentralDatabase In Support of Field Representative Territory Assignments,”which is each hereby incorporated by reference in its entirety.

Once the results-cache-table has been populated with the data recordsidentified as satisfying the search criteria at step 475S, theresults-cache-table records can be inserted into the client-cache-tableon the client at step 480. The client-cache-table can then be merged inthe client database at step 485, and the last sync time updated on theclient and/or server 490. The merger and synchronization can becommitted at step 495.

Note that while the above discussion references client-to-serversynchronization and server-to-client synchronization as two separateprocesses, the steps involved are very similar, and in some cases canmirror each other. Additionally, synchronization in both directions canbe performed simultaneously, thereby reducing much of the processing andoverhead required for synchronization. For example, establishing acommunication channel between the server and the client, steps 310 and410, is only required once. Additionally, the last sync time determinedin steps 320 and 420 can be determined once and used to synchronize inboth directions. However, it can be beneficial to store twosynchronization times, one for client-to-server synchronization, andanother for server-to-client synchronization. Similarly, thedetermination of whether synchronization is necessary (steps 330 and430) and the identification of tables to be synchronized (steps 340 and440) can be combined to determine the parameters for bothsynchronization directions.

Furthermore, in the foregoing description, certain flow diagrams havebeen shown and processes described in relation to those flow diagramswhich provide a reference for discussion purposes. In an actualimplementation of the methods of the present invention, the steps cancomprise event-driven routines that can run in parallel and can belaunched and executed other than as shown by the simple depiction in theflow diagrams. In short, the particular order of the steps in the flowdiagrams is illustrative of the invention, but not limiting of thevarious permutations that can be achieved in a given embodiment.Accordingly, it is the performance of the steps recited in the claimsappended below which is pertinent, and not the order of operation of thesteps themselves.

While the invention has been described in connection with a certainembodiment thereof and in relation to a particular environment, theinvention is not limited to the described embodiments and environmentsbut rather is more broadly defined by the recitations in the claimsbelow and equivalents thereof.

We claim:
 1. A computer-assisted method for synchronizing records in aserver database with a client database at a client which is configurableto have a client-cache-table with one or more fields corresponding to atleast one field within the client database, comprising the steps of:establishing a communication connection between a server and the client,wherein the server and the client comprise respective machines;determining that a synchronization session is necessary with regard toone or more of the records; creating a server-cache-table on the serverhaving one or more fields corresponding to at least one field in theserver database in response to the determining step having determinedthat the synchronization session is necessary, wherein theserver-cache-table is a temporary table designed to hold data for thesynchronization session; populating the server-cache-table in accordancewith a synchronization criterion using at least one database recordwithin the server database, wherein the synchronization criterioncomprises a test or rule applied to the at least one database record ofthe server database; transmitting the at least one database record fromthe populated server-cache-table to the client over a network using thecommunication connection to cause a synchronization in which theclient-cache-table commits the transmitted at least one database recordfrom the populated server-cache-table into the client database; andreceiving a notification at the server database that the at least onedatabase record has been committed to the client database.
 2. The methodof claim 1, wherein the server-cache-table comprises a temporary table.3. The method of claim 1, wherein the test or rule comprising thesynchronization criterion is satisfied in view of a type of data, anowner of the data, or a priority of the data included in the databaserecords within the server database.
 4. The method of claim 1, furthercomprising the step of determining a last sync time, wherein the test orrule comprising the synchronization criterion verifies that amodification status of the at least one database record has beenmodified since the last sync time.
 5. The method of claim 4, wherein thelast sync time is associated with the at least one field in the serverdatabase.
 6. The method of claim 4, further comprising the step ofretrieving the last sync time from the server for the client.
 7. Themethod of claim 1, further comprising the step of identifying a set ofrelevant tables, wherein the test or rule comprising the synchronizationcriterion verifies the at least one database record is included in theset of relevant tables.
 8. The method of claim 1, wherein thecommunication connection includes a database link.
 9. The method ofclaim 1, further comprising the step of updating a last sync time. 10.The method of claim 1, wherein the committing step includes the step ofupdating a record status to indicate that the record has been deleted.11. A computer-based system for synchronizing records within a clientdatabase to a current status of a server database, comprising: a servercomprising a computer, wherein the server database is associated withthe server; a communication connection that is connectable over anetwork from the server to the client database; wherein the server isconfigured to: be in a synchronization session, wherein thesynchronization session is established by either the server or theclient over the communication connection; create a server-cache-tablehaving a structure compatible with the client database for use in thesynchronization session, wherein the server-cache-table is a temporarytable designed to hold data for the synchronization session; populatethe server-cache table with at least one database record from the serverdatabase in accordance with a synchronization criterion applied to theat least one database record of the server database, wherein thesynchronization criterion comprises a test or rule applied to the atleast one database record of the server database; transmit through thecommunication connection the at least one database record from thepopulated server-cache table to the client database; and receive anotification at the server database that the at least one databaserecord has been committed to the client database, whereby the clientdatabase is synchronized to the current status of the server database.12. The computer-based system of claim 11, wherein theserver-cache-table comprises a temporary table.
 13. The computer-basedsystem of claim 11, wherein the test or rule comprising thesynchronization criterion is satisfied in view of a type of data, anowner of the data, or a priority of the data included in the databaserecords within the server database.
 14. The computer-based system ofclaim 11, wherein there are a plurality of client databases and whereinthe server is configured to determine a last sync time with the clientdatabase among the plurality of client databases, wherein the test orrule comprising the synchronization criterion verifies that amodification status of the at least one database record has beenmodified since the last sync time with the client database.
 15. Thecomputer-based system of claim 14, wherein the server is furtherconfigured to update the last sync time.
 16. The computer-based systemof claim 11, wherein the server is further configured to clear theserver-cache-table after the notification.